Mega Code Archive
Analytical Functions
1) A different ordering with the use of analytical functions
2) A seven-day MAX and MIN on Tuesdays
3) A SUM using ROLLUP
4) A two-dimensional grouping with ROLLUP
5) Adding an Analytical Function to a Query that Contains a Join (and Other WHERE Conditions)
6) Adding an Analytical Function to the GROUP BY with ORDER BY Version
7) An Application of GROUPING_ID()
8) An Expanded Example of a Physical Window
9) AVG(salary) OVER
10) Calculate with Analytical functions
11) Case with grouping and rollup
12) Changing the Final Ordering after Having Added an Analytical Function
13) Changing the Position of Columns Passed to ROLLUP
14) Computing the GROUPING Bit Vector
15) Count employees, group by CUBE(department no, job title)
16) Count employees, group by ROLLUP(department no, job title)
17) CUBE
18) CUME_DIST functions
19) CUME_DIST() calculates the position of a specified value relative to a group of values
20) Cume_dist over
21) Decode dense_rank
22) Default is NULLS FIRST
23) DENSE_RANK NULL value
24) Depict the rank of the salaries with ROWNUM
25) Desacending order
26) Displaying a Running Total Using SUM as an Analytical Function
27) Displaying which rows are used in the moving average calculations with two other analytical functions
28) Eliminate duplicate rows using a HAVING clause that only allows rows whose GROUP_ID() is 0
29) FIRST functions return the first values in an ordered group
30) Get a clearer picture of the NTILE function
31) Getting the First Rows Using FIRST_VALUE()
32) Getting the Last Rows Using LAST_VALUE()
33) Getting Values and Subtotals in One Go with GROUPING SETS
34) Group by CUBE(department no, job title)
35) Group by ROLLUP(department no, job title)
36) Group_id with cube
37) GROUPING() with a Single Column in a ROLLUP
38) GROUPING() with CUBE demo
39) Grouping with Rollup and Cube
40) Grouping_id function with case statement
41) Include two columns in CUBE function
42) LAG and LEAD Options
43) Lag salary over, lead salary over
44) LAST functions return the last values in an ordered group
45) More Than One Analytical Function May Be Used in a Single Statement
46) Moving average
47) NTILE function works from row order after a ranking takes place
48) NTILE groups data by sort order into a variable number of percentile groupings
49) NTILE with NULLS FIRST (the default)
50) NTile with NULLS LAST
51) Nulls and Analytical Functions
52) Nulls and Analytical Functions ROW_NUMBER
53) Nulls could also be handled with a default value using the NVL function in the analytical function
54) NULLS FIRST
55) NULLS FIRST demo
56) NULLS LAST
57) NULLS LAST and the ROW_NUMBER
58) NULLS LAST demo
59) Nulls may be excluded with a WHERE clause when using Analytical functions
60) Nulls with the NTILE function
61) NVL without NULLS LAST
62) Order by range unbounded preceding
63) PARTITION BY (JOB title) and right outer join
64) PARTITION BY clause divides the groups into subgroups
65) PARTITION BY, order by and range unbounded preceding
66) Partitioning with PARTITION_BY
67) Passing Multiple Columns to ROLLUP
68) PERCENT_RANK
69) PERCENT_RANK() calculates the percent rank of a value relative to a group of values
70) Performing a Centered Average
71) Performing a Cumulative Sum
72) Performing a Moving Average
73) RANK NULL value
74) Ratio-to-Report
75) Restrict the MEASURESRULES to cover only one of the dimensions
76) Rollup with two columns
77) ROUND(AVG(salary) OVER())
78) ROW_NUMBER function with an ordering on salary in descending order
79) ROW_NUMBER() with Partition
80) ROW_NUMBER() with Partition demo
81) Row-ordering is done first and then the moving average
82) See the top five salaries
83) See the top five salaries (DENSE_RANK)
84) Select percentile_cont(0 5) within group (order by salary desc )
85) SPREADSHEET
86) SUM analytical function can easily be partioned
87) Sum over (nothing)
88) Sum over partition by, order by
89) Sum salary over PARTITION BY
90) Sum with Order by range unbounded preceding
91) SUM, Partition and order by
92) Take a probability value (between 0 and 1) and returns a percentile value (for a continuous distribution)
93) The Analytical Functions in Oracle (Analytical Functions I)
94) The DENSE_RANK and tie
95) The Join Without the Analytical Function
96) The MODEL statement does calculations on a column in a row based on other rows in a result set
97) The Order in Which the Analytical Function Is Processed in the SQL Statement
98) The RANK function will not only produce the row numbering but will skip a rank if there is a tie
99) The ROLLUP function was provided to conveniently give the sum on the aggregate
100) The row comparison function partitioned as with other aggregates
101) The Row Comparison Functions - LEAD and LAG
102) There is an option to place nulls first or last with the analytical function
103) To depict the rank of the salaries in descending order with ROWNUM
104) Unbounded Following
105) Use an analytical function in a WHERE clause
106) Use CUBE and RANK() to get all rankings of salaries by city and description
107) Use GROUPING SETS and RANK() to get just the salary subtotal rankings
108) Use GROUPING(x) function in a DECODE or CASE to enhance the result
109) Use partitioning in the OVER clause of the aggregate-analytical function
110) Use PERCENT_RANK Function
111) Use RANK(), PERCENT_RANK(), CUME_DIST()
112) Use ROW_NUMBER(), RANK() and DENSE_RANK() together
113) Use ROWS UNBOUNDED PRECEDING to implicitly indicate the end of the window is the current row
114) Use SUM for windowing
115) Use the BREAK reporting tool to space the display conveniently
116) Use the COUNT aggregate analytical function to show how many rows are included in each window
117) Uses AVG() with ROLLUP
118) Using a Column Multiple Times in a GROUP BY Clause
119) Using Analytic Functions
120) Using Analytic Functions AVG(Mark) OVER (PARTITION BY StudentID ORDER BY StudentID, Mark)
121) Using DECODE() and GROUPING() to Convert Multiple Column Values
122) Using DECODE() to Convert the Returned Value from GROUPING()
123) Using GROUPING() with CUBE
124) Using HAVING with an Analytical Function
125) Using NULLS FIRST
126) Using NULLS LAST and the ROW_NUMBER
127) Using ROLLUP, CUBE, and GROUPING SETS Operators with Analytic Functions
128) Using the Analytic Functions
129) Using the CUBE Clause
130) Using the DENSE_RANK()
131) Using the GROUP_ID() Function
132) Using the GROUPING SETS Clause
133) Using the Hypothetical Rank and Distribution Functions
134) Using the LAG() and LEAD() Functions
135) Using the NTILE() Function
136) Using the RANK()
137) Using the Ranking Functions
138) Using the RATIO_TO_REPORT() Function
139) Using the ROLLUP Clause
140) Using the ROW_NUMBER() Function
141) Using the ROWNUM function
142) Using the Window Functions
143) WHERE is applied before the RANK()